﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Configuration;
using System.Web;
using OGS.Contracts;


namespace OGS.DataAccess
{
    public enum DBServerType : int
    {
        CustomServiceDB = 0,
        KenticoCMSDB = 1,
        KenticoeCommerceDB = 2,

    }

    /// <summary>
    /// Summary description for DBManager
    /// </summary>
    internal class DBManager : DAConnection, IDisposable
    {
        //public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;

        #region Constructor ( 2+ overloads)
        /// <summary>
        /// DBManager constructor 
        /// </summary>
        /// <param name="CommandText">string to be executed</param>
        public DBManager(string CommandText)
            : base()
        {
            this.CommandText = CommandText;
            SetSqlCommand();
        }

        /// <summary>
        /// DBManager constructor 
        /// </summary>
        /// <param name="CommandText">string to be executed</param>
        /// <param name="CurrentCommandType">CommandType</param>
        public DBManager(string CommandText, CommandType CurrentCommandType)
            : this(CommandText)
        {
            this.CurrentCommandType = CurrentCommandType;
        }

        /// <summary>
        /// DBManager constructor 
        /// </summary>
        /// <param name="CommandText">string to be executed</param>
        public DBManager(string CommandText, SearchAttributes oSearchAttributes)
            : this(CommandText)
        {

            if (!oSearchAttributes.FilterCharacter.Equals((char)32))
            {
                this.Add("@FilterCharacter", oSearchAttributes.FilterCharacter);
            }
            this.Add("@SortColumnIndex", oSearchAttributes.SortColumnIndex);
            this.Add("@SortDirection", (int)oSearchAttributes.SortDirection == 1 ? 1 : 0);
            this.Add("@CurrentPageNumber", oSearchAttributes.CurrentPageNumber);
            this.Add("@RecordsPerPage", oSearchAttributes.RecordsPerPage);
            this.Add("@TotalRecordCount", 0, ParameterDirection.Output);
            if (!string.IsNullOrEmpty(oSearchAttributes.SearchText))
            {
                this.Add("@SearchText", oSearchAttributes.SearchText);
            }

        }

        /// <summary>
        /// Finalizer
        /// </summary>
        ~DBManager()
        {
            Dispose(false);
        }

        #endregion

        #region Dispose
        /// <summary>
        /// Releases the resources used by the Component.
        /// </summary>
        /// <param name="disposing">Send true value when Dispose Method is called by the program</param>
        public void Dispose(bool disposing)
        {
            if (disposing)
            {
                GC.SuppressFinalize(this);
            }
            _oSqlCommand.Parameters.Clear();
            if (_oSqlCommand.Connection != null)
            {
                if (_oSqlCommand.Connection.State == System.Data.ConnectionState.Open)
                {
                    _oSqlCommand.Connection.Close();
                }
            }
            _oSqlCommand.Dispose();
        }

        /// <summary>
        /// Releases the resources used by the Component.
        /// </summary>
        void Dispose()
        {
            Dispose(true);
        }

        #region IDisposable Members
        /// <summary>
        /// Releases the resources used by the Component.
        /// </summary>
        void IDisposable.Dispose()
        {
            Dispose(true);
        }
        #endregion

        #endregion

        #region SetSqlCommand
        /// <summary>
        /// Sets up the sql Command
        /// </summary>
        private void SetSqlCommand()
        {
            if (_oSqlCommand == null) { _oSqlCommand = new SqlCommand(_CommandText); }
            else { _oSqlCommand.CommandText = _CommandText; }
            _oSqlCommand.CommandType = DefaultCommandType;
            _oSqlCommand.CommandTimeout = 1200;
        }
        #endregion

        #region Add Method
        /// <summary>
        /// Adds parameter name and value for the stored procedures. Parameter name will be same as found in stored procedures
        /// </summary>
        /// <param name="parameterName">Name of the parameter used in stored procedures</param>
        /// <param name="value">Value to be passed in stored procedures</param>
        /// <returns>Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.</returns>
        public SqlParameter Add(string parameterName, object value)
        {
            return this.Add(new SqlParameter(parameterName, value));

        }
        /// <summary>
        /// Adds parameter name ,value and direction for the stored procedures. Parameter name will be same as found in stored procedures
        /// </summary>
        /// <param name="parameterName">Name of the parameter used in stored procedures</param>
        /// <param name="value">Value to be passed in stored procedures</param>
        /// <param name="direction">One of the value of ParameterDirection for the parameter</param>
        /// <returns>Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.</returns>
        public SqlParameter Add(string parameterName, object value, System.Data.ParameterDirection direction)
        {
            return this.Add(new SqlParameter(parameterName, value), direction);
        }
        /// <summary>
        /// Adds parameter name, datatype and direction for the stored procedures. Parameter name will be same as found in stored procedures
        /// </summary>
        /// <param name="parameterName">Name of the parameter used in stored procedures</param>
        /// <param name="sqlDbType">One of the SqlDbType values used in stored procedures</param>
        /// <param name="direction">One of the ParameterDirection values</param>
        /// <returns>Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.</returns>
        public SqlParameter Add(string parameterName, System.Data.SqlDbType sqlDbType, System.Data.ParameterDirection direction)
        {
            return this.Add(new SqlParameter(parameterName, sqlDbType), direction);
        }
        /// <summary>
        /// Adds parameter name, datatype and direction for the stored procedures. Parameter name will be same as found in stored procedures
        /// </summary>
        /// <param name="parameterName">Name of the parameter used in stored procedures</param>
        /// <param name="sqlDbType">One of the SqlDbType values used in stored procedures</param>
        /// <param name="direction">One of the ParameterDirection values</param>
        /// <param name="value">Value to be passed in stored procedures</param>
        /// <returns>Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.</returns>
        public SqlParameter Add(string parameterName, System.Data.SqlDbType sqlDbType, object value)
        {
            return this.Add(new SqlParameter(parameterName, sqlDbType), ParameterDirection.Input, value);
        }
        /// <summary>
        /// Adds parameter name, datatype and direction for the stored procedures. Parameter name will be same as found in stored procedures
        /// </summary>
        /// <param name="parameterName">Name of the parameter used in stored procedures</param>
        /// <param name="sqlDbType">One of the SqlDbType values used in stored procedures</param>
        /// <param name="direction">One of the ParameterDirection values</param>
        /// <param name="value">Value to be passed in stored procedures</param>
        /// <returns>Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.</returns>
        public SqlParameter Add(string parameterName, System.Data.SqlDbType sqlDbType, System.Data.ParameterDirection direction, object value)
        {
            return this.Add(new SqlParameter(parameterName, sqlDbType), direction, value);
        }
        /// <summary>
        /// Adds parameter name, datatype, size and direction for the stored procedures. Parameter name will be same as found in stored procedures
        /// </summary>
        /// <param name="parameterName">Name of the parameter used in stored procedures</param>
        /// <param name="sqlDbType">One of the SqlDbType values used in stored procedures</param>
        /// <param name="size">The length of the column.</param>
        /// <param name="direction">One of the ParameterDirection values</param>
        /// <returns>Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.</returns>
        public SqlParameter Add(string parameterName, System.Data.SqlDbType sqlDbType, int size, System.Data.ParameterDirection direction)
        {
            return this.Add(new SqlParameter(parameterName, sqlDbType, size), direction);
        }
        /// <summary>
        /// Adds parameter name, datatype, size and direction for the stored procedures. Parameter name will be same as found in stored procedures
        /// </summary>
        /// <param name="parameterName">Name of the parameter used in stored procedures</param>
        /// <param name="sqlDbType">One of the SqlDbType values used in stored procedures</param>
        /// <param name="size">The length of the column. </param>
        /// <param name="direction">One of the ParameterDirection values</param>
        /// <param name="value">Value to be passed in stored procedures</param>
        /// <returns>Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.</returns>
        public SqlParameter Add(string parameterName, System.Data.SqlDbType sqlDbType, int size, object value)
        {
            return this.Add(new SqlParameter(parameterName, sqlDbType, size), ParameterDirection.Input, value);
        }
        /// <summary>
        /// Adds parameter name, datatype, size and direction for the stored procedures. Parameter name will be same as found in stored procedures
        /// </summary>
        /// <param name="parameterName">Name of the parameter used in stored procedures</param>
        /// <param name="sqlDbType">One of the SqlDbType values used in stored procedures</param>
        /// <param name="size">The length of the column. </param>
        /// <param name="direction">One of the ParameterDirection values</param>
        /// <param name="value">Value to be passed in stored procedures</param>
        /// <returns>Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.</returns>
        public SqlParameter Add(string parameterName, System.Data.SqlDbType sqlDbType, int size, System.Data.ParameterDirection direction, object value)
        {
            return this.Add(new SqlParameter(parameterName, sqlDbType, size), direction, value);
        }
        public SqlParameter Add(string parameterName, System.Data.SqlDbType sqlDbType, int size, System.Data.ParameterDirection direction, bool isNullable)
        {
            SqlParameter sParam = new SqlParameter(parameterName, sqlDbType, size);
            sParam.Direction = direction;
            sParam.IsNullable = isNullable;
            return this.Add(sParam);
        }
        /// <summary>
        ///  Adds SqlParameter object 
        /// </summary>
        /// <param name="SqlPar">The SqlParameter to add to the collection.</param>
        /// <returns>Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.</returns>
        public SqlParameter Add(SqlParameter SqlPar)
        {
            _oSqlCommand.CommandType = CommandType.StoredProcedure;
            return _oSqlCommand.Parameters.Add(SqlPar);
        }
        /// <summary>
        /// Adds SqlParameter object 
        /// </summary>
        /// <param name="SqlPar">The SqlParameter to add to the collection.</param>
        /// <param name="direction">One of the value of ParameterDirection for the parameter</param>
        /// <returns>Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.</returns>
        private SqlParameter Add(SqlParameter SqlPar, System.Data.ParameterDirection direction)
        {
            _oSqlCommand.CommandType = CommandType.StoredProcedure;
            SqlPar.Direction = direction;
            return _oSqlCommand.Parameters.Add(SqlPar);
        }
        /// <summary>
        /// Adds SqlParameter object 
        /// </summary>
        /// <param name="value">The SqlParameter to add to the collection.</param>
        /// <param name="direction">One of the value of ParameterDirection for the parameter</param>
        /// <param name="value">Value to be passed in stored procedures</param>
        /// <returns>Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.</returns>
        private SqlParameter Add(SqlParameter SqlPar, System.Data.ParameterDirection direction, object value)
        {
            _oSqlCommand.CommandType = CommandType.StoredProcedure;
            SqlPar.Value = value ?? DBNull.Value;
            SqlPar.Direction = direction;
            return _oSqlCommand.Parameters.Add(SqlPar);
        }
        #endregion

        #region Clear Method
        /// <summary>
        /// Removes all Parameter added.
        /// </summary>
        public void Clear()
        {
            _oSqlCommand.Parameters.Clear();
            //mCommandType = CommandType.Text;
        }
        #endregion

        #region Contains
        /// <summary>
        /// Gets a value indicating whether a SqlParameter exists in the collection
        /// </summary>
        /// <param name="value">The value of the SqlParameter object to find</param>
        /// <returns>true if the collection contains the parameter; otherwise, false.</returns>
        public bool Contains(object value)
        {
            return _oSqlCommand.Parameters.Contains(value);
        }
        /// <summary>
        /// Gets a value indicating whether a SqlParameter with the specified parameter name exists in the collection.
        /// </summary>
        /// <param name="value">The name of the SqlParameter object to find. </param>
        /// <returns>true if the collection contains the parameter; otherwise, false.</returns>
        public bool Contains(string value)
        {
            return _oSqlCommand.Parameters.Contains(value);
        }
        #endregion

        #region GetEnumerator
        public System.Collections.IEnumerator GetEnumerator()
        {
            return _oSqlCommand.Parameters.GetEnumerator();
        }
        #endregion

        #region IndexOf Method
        /// <summary>
        /// Gets the location of a SqlParameter in the collection.
        /// </summary>
        /// <param name="value">The SqlParameter object to locate. </param>
        /// <returns>The zero-based location of the SqlParameter in the collection.</returns>
        public int IndexOf(object value)
        {
            return _oSqlCommand.Parameters.IndexOf(value);
        }
        /// <summary>
        /// Gets the location of the SqlParameter in the collection with a specific parameter name.
        /// </summary>
        /// <param name="parameterName">The name of the SqlParameter object to retrieve. </param>
        /// <returns>The zero-based location of the SqlParameter in the collection.</returns>
        public int IndexOf(string parameterName)
        {
            return _oSqlCommand.Parameters.IndexOf(parameterName);
        }
        #endregion

        #region Insert Method
        /// <summary>
        /// Inserts a SqlParameter into the collection at the specified index.
        /// </summary>
        /// <param name="index">The zero-based index where the parameter is to be inserted within the collection.</param>
        /// <param name="value">The SqlParameter to add to the collection. </param>
        public void Insert(int index, object value)
        {
            _oSqlCommand.Parameters.Insert(index, value);
        }
        #endregion

        #region Remove Method
        /// <summary>
        /// Removes the specified SqlParameter from the collection.
        /// </summary>
        /// <param name="value">A SqlParameter object to remove from the collection. </param>
        public void Remove(object value)
        {
            //if (_oSqlCommand.Parameters.Count == 0)
            //    mCommandType = CommandType.Text;
            _oSqlCommand.Parameters.Remove(value);
        }
        #endregion

        #region RemoveAt Method
        /// <summary>
        /// Removes the specified SqlParameter from the collection using the parameter name.
        /// </summary>
        /// <param name="parameterName">The name of the SqlParameter object to retrieve.</param>
        public void RemoveAt(string parameterName)
        {
            //if (_oSqlCommand.Parameters.Count == 0)
            //    mCommandType = CommandType.Text;
            _oSqlCommand.Parameters.RemoveAt(parameterName);
        }
        /// <summary>
        /// Removes the specified SqlParameter from the collection using a specific index.
        /// </summary>
        /// <param name="index">The zero-based index of the parameter.</param>
        public void RemoveAt(int index)
        {
            _oSqlCommand.Parameters.RemoveAt(index);
        }
        #endregion

        #region ReturnValue
        /// <summary>
        /// Returns the value from Stored Procedures
        /// </summary>
        public int ReturnValue
        {
            get
            {
                if (this["@ReturnValue"].Value == DBNull.Value)
                    return int.MinValue;
                return (int)this["@ReturnValue"].Value;
            }
        }
        #endregion

        #region SQL Execution By Return Object
        /// <summary>
        /// 
        /// </summary>
        /// <param name="CommandText"></param>
        /// <returns></returns>
        private System.Data.SqlClient.SqlConnection PrepareExecution(string CommandText)
        {
            if (_oSqlCommand == null)
            {
                _oSqlCommand = new System.Data.SqlClient.SqlCommand();
            }
            if (_oSqlCommand.Connection == null)
            {
                _oSqlCommand.Connection = new System.Data.SqlClient.SqlConnection(ConnectionString);
            }
            else if (_oSqlCommand.Connection.ConnectionString.Length == 0)
            {
                _oSqlCommand.Connection.ConnectionString = ConnectionString;
            }
            if (_oSqlCommand.Connection.State != System.Data.ConnectionState.Open)
            {
                _oSqlCommand.Connection.Open();
            }
            _oSqlCommand.CommandText = CommandText;
            if (_oSqlCommand.CommandType == CommandType.StoredProcedure)
            {
                Add("@ReturnValue", SqlDbType.Int, ParameterDirection.ReturnValue);
            }

            return _oSqlCommand.Connection;
        }
        #endregion

        #region Property of ExecuteNonQuery
        /// <summary>
        /// Executes a Transact-SQL statement against the connection and returns the number of rows affected.
        /// </summary>
        /// <param name="CommandText">Executes the Transact-SQL statement or stored procedure against the connection.</param>
        /// <returns>The number of rows affected.</returns>
        public int ExecuteNonQuery()
        {
            using (PrepareExecution(CommandText))
            {
                return _oSqlCommand.ExecuteNonQuery();
            }
        }
        #endregion

        #region public object ExecuteScalar
        /// <summary>
        /// Executes a Transact-SQL statement against the connection and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.
        /// </summary>
        /// <param name="CommandText">Executes the Transact-SQL statement or stored procedure against the connection.</param>
        /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
        public object ExecuteScalar()
        {
            using (PrepareExecution(CommandText))
            {
                return _oSqlCommand.ExecuteScalar();
            }
        }
        #endregion

        #region public SqlDataReader ExecuteReader ( 1+ overloads)
        /// <summary>
        /// Executes a Transact-SQL statement against the connection and returns a stream of (reading & forward-only) rows from a SQL Server database.
        /// </summary>
        /// <param name="CommandText">Executes the Transact-SQL statement or stored procedure against the connection.</param>
        /// <returns>A SqlDataReader object.</returns>
        public SqlDataReader ExecuteReader()
        {
            PrepareExecution(CommandText);
            return _oSqlCommand.ExecuteReader();
        }

        /// <summary>
        /// Executes a Transact-SQL statement against the connection and returns a stream of (reading & forward-only) rows from a SQL Server database.
        /// </summary>
        /// <param name="CommandText">Executes the Transact-SQL statement or stored procedure against the connection.</param>
        /// <param name="behavior">One of the CommandBehavior values.</param>
        /// <returns>A SqlDataReader object.</returns>
        public SqlDataReader ExecuteReader(CommandBehavior behavior)
        {
            PrepareExecution(CommandText);
            return _oSqlCommand.ExecuteReader(behavior);

        }
        #endregion

        #region DataTable GetDataTable
        /// <summary>
        /// Executes a Transact-SQL statement against the connection and returns represents one table of in-memory data
        /// </summary>
        /// <param name="CommandText">Executes the Transact-SQL statement or stored procedure against the connection.</param>
        /// <returns>An DataTable object</returns>
        public DataTable GetDataTable()
        {
            using (PrepareExecution(CommandText))
            {
                using (System.Data.SqlClient.SqlDataAdapter SqlAdp = new System.Data.SqlClient.SqlDataAdapter(_oSqlCommand))
                {
                    System.Data.DataTable dataTable = new System.Data.DataTable();
                    SqlAdp.Fill(dataTable);
                    return dataTable;
                }
            }
        }
        #endregion

        #region DataSet GetDataSet
        /// <summary>
        /// Executes a Transact-SQL statement against the connection and returns dataset of in-memory data
        /// </summary>
        /// <param name="CommandText">Executes the Transact-SQL statement or stored procedure against the connection.</param>
        /// <returns>A Dataset object</returns>
        public DataSet GetDataSet()
        {
            using (PrepareExecution(CommandText))
            {
                using (System.Data.SqlClient.SqlDataAdapter SqlAdp = new System.Data.SqlClient.SqlDataAdapter(_oSqlCommand))
                {
                    System.Data.DataSet dataSet = new System.Data.DataSet();
                    SqlAdp.Fill(dataSet);
                    if (dataSet.Tables.Count > 0)
                    {
                        return dataSet;
                    }
                    else
                    {
                        return (new DataSet());
                    }
                }
            }
        }
        #endregion

        #region XmlReader ExecuteXmlReader
        /// <summary>
        /// Executes a Transact-SQL statement against the connection and returns provides a stream of XML data which is forward-only, read-only access.
        /// </summary>
        /// <param name="CommandText">Executes the Transact-SQL statement or stored procedure against the connection.</param>
        /// <returns>An XmlReader object.</returns>
        public XmlReader ExecuteXmlReader()
        {
            PrepareExecution(CommandText);
            return _oSqlCommand.ExecuteXmlReader();
        }
        #endregion

        #region Get Values
        public T GetValue<T>(string columnName)
        {
            if (this[columnName].Value != DBNull.Value)
            {
                return (T)this[columnName].Value;
            }
            return default(T);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public string GetValueInString(string columnName)
        {
            if (this[columnName].Value != DBNull.Value)
            {
                return this[columnName].Value as string;
            }
            return null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public int? GetValueInInt(string columnName)
        {
            if (this[columnName].Value != DBNull.Value)
            {
                int i = Convert.ToInt32(this[columnName].Value);
                return i;
            }
            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public long? GetValueInLong(string columnName)
        {
            if (this[columnName].Value != DBNull.Value)
            {
                long i = Convert.ToInt64(this[columnName].Value);
                return i;
            }
            return null;
        }

        // <summary>
        /// 
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public char? GetValueInChar(string columnName)
        {
            if (this[columnName].Value != DBNull.Value)
            {
                if (this[columnName].Value.GetType() == typeof(string))
                {
                    if (this[columnName].Value.ToString().Length > 0)
                    {
                        return this[columnName].Value.ToString()[0];
                    }
                    else
                    {
                        return null;
                    }
                }
                return (char)this[columnName].Value;
            }
            return null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public byte? GetValueInByte(string columnName)
        {
            if (this[columnName].Value != DBNull.Value)
            {
                return (byte)this[columnName].Value;
            }
            return null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public DateTime? GetValueInDateTime(string columnName)
        {
            if (this[columnName].Value != DBNull.Value)
            {
                return (DateTime)this[columnName].Value;
            }
            return null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public bool GetValueInBool(string columnName)
        {
            if (this[columnName].Value != DBNull.Value)
            {
                return (bool)this[columnName].Value;
            }
            return false;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public decimal? GetValueInDecimal(string columnName)
        {
            if (this[columnName].Value != DBNull.Value)
            {
                return (decimal)this[columnName].Value;
            }
            return null;
        }

        #endregion

        #region Properties

        #region public string CommandText
        private string _CommandText;
        /// <summary>
        /// 
        /// </summary>
        public string CommandText
        {
            get { return _CommandText; }
            set { _CommandText = value; SetSqlCommand(); }
        }
        #endregion

        #region CommandType DefaultCommandType
        private CommandType _DefaultCommandType = CommandType.StoredProcedure;
        public CommandType DefaultCommandType
        {
            get { return _DefaultCommandType; }
            set { _DefaultCommandType = value; }
        }
        #endregion

        #region CommandType CurrentCommandType
        public CommandType CurrentCommandType
        {
            get { return _oSqlCommand.CommandType; }
            set { _oSqlCommand.CommandType = value; }
        }
        #endregion

        #region public SqlCommand oSqlCommand
        private SqlCommand _oSqlCommand;
        /// <summary>
        /// 
        /// </summary>
        public SqlCommand oSqlCommand
        {
            get { return _oSqlCommand; }
            set { _oSqlCommand = value; }
        }
        #endregion

        #region Count Property
        /// <summary>
        /// Gets the number of SqlParameter objects in the collection.
        /// </summary>
        public int Count
        {
            get
            {
                return _oSqlCommand.Parameters.Count;
            }
        }
        #endregion

        #region Indexers Property
        /// <summary>
        /// The parameters of the Transact-SQL statement or stored procedure. The default is an empty collection.
        /// </summary>
        public SqlParameter this[int index]
        {
            get { return _oSqlCommand.Parameters[index]; }
            set { _oSqlCommand.Parameters[index] = value; }

        }
        /// <summary>
        /// The parameters of the Transact-SQL statement or stored procedure. The default is an empty collection.
        /// </summary>
        public SqlParameter this[string parameterName]
        {
            get { return _oSqlCommand.Parameters[parameterName]; }
            set { _oSqlCommand.Parameters[parameterName] = value; }
        }
        #endregion

        #endregion

    }
}
